package com.yss.sofa.report.engine.poi;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.DateUtil;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class SheetUtils {
	// public static void main(String[] args) throws Exception {
	// SheetUtils t = new SheetUtils();
	// t.copyWbSheet("D:/test/excel/8-4.历次考试成绩汇总-1.xlsx",
	// "D:/test/excel/8-4.历次考试成绩汇总-2.xlsx", "8-4.历次考试成绩汇总", null);
	// }

	// destSheetName为null时候使用sheetName的值为destFileName的sheet名
	public void copyWbSheet(String srcfileName, String destFileName,
			String sheetName, String destSheetName) throws Exception {
		File srcFile = new File(srcfileName);
		if (!srcFile.exists()) {
			return;
		}
		XSSFWorkbook srcwb = new XSSFWorkbook(new FileInputStream(srcfileName));
		XSSFSheet srcSheet = null;
		if (sheetName == null) {
			srcSheet = srcwb.getSheetAt(0);
			sheetName = srcwb.getSheetName(0);
		} else {
			srcSheet = srcwb.getSheet(sheetName);
		}
		if (srcSheet == null) {
			return;
		}
		if (destSheetName == null) {
			destSheetName = sheetName;
		}
		XSSFWorkbook destwb = new XSSFWorkbook();
		XSSFSheet destSheet = null;
		File destFile = new File(destFileName);
		// 不存在则新建
		if (!destFile.exists()) {
			destFile.createNewFile();
		} else {
			destwb = new XSSFWorkbook(new FileInputStream(destFile));
		}
		destSheet = destwb.getSheet(destSheetName);
		if (destSheet == null) {
			destSheet = destwb.createSheet(destSheetName);
		}
		// 最大列数
		int maxCellNum = copySheet(srcSheet, destSheet, srcwb, destwb,false);
		// 设置列宽
		setSheetWidth(srcSheet, destSheet, maxCellNum);
		// 合并单元格
		mergeSheetAllRegion(srcSheet, destSheet);
		// 保存
		saveFile(destwb, destFileName);
	}

	public static Workbook copyWbSheet(Workbook srcwb, String destFileName,
			String sheetName, String destSheetName) throws Exception {
		Sheet srcSheet = null;
		if (sheetName == null) {
			srcSheet = srcwb.getSheetAt(0);
			sheetName = srcwb.getSheetName(0);
		} else {
			srcSheet = srcwb.getSheet(sheetName);
		}
		if (srcSheet == null) {
			return null;
		}
		if (destSheetName == null) {
			destSheetName = sheetName;
		}
		XSSFWorkbook destwb = new XSSFWorkbook();
		XSSFSheet destSheet = null;
		File destFile = new File(destFileName);
		// 不存在则新建
		if (!destFile.exists()) {
			destFile.createNewFile();
		} else {
			destwb = new XSSFWorkbook(new FileInputStream(destFile));
		}
		destSheet = destwb.getSheet(destSheetName);
		if (destSheet == null) {
			destSheet = destwb.createSheet(destSheetName);
		}
		// 最大列数
		int maxCellNum = copySheet(srcSheet, destSheet, srcwb, destwb,false);
		// 设置列宽
		setSheetWidth(srcSheet, destSheet, maxCellNum);
		// 合并单元格
		mergeSheetAllRegion(srcSheet, destSheet);
		// 保存
		saveFile(destwb, destFileName);
		return destwb;
	}

	public static void saveFile(Workbook destwb, String destFileName) {
		try {
			FileOutputStream fileOutStream = new FileOutputStream(destFileName);
			destwb.write(fileOutStream);
			if (fileOutStream != null) {
				fileOutStream.close();
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static int copySheet(Sheet srcSheet, Sheet destSheet,
			Workbook srcwb, Workbook destwb,boolean isCopyStyle) throws Exception {
		int rowCount = srcSheet.getLastRowNum();// 总行数
		int maxCellNum = 0;
		Row srcRow = null, destRow = null;
		// 注意这里
		for (int i = 0; i <= rowCount; i++) {
			srcRow = srcSheet.getRow(i);
			destRow = destSheet.getRow(i);
			if (srcRow == null) {
				continue;
			}
			// 最大列数
			maxCellNum = maxCellNum < srcRow.getLastCellNum() ? srcRow.getLastCellNum() : maxCellNum;
			if (destRow == null) {
				destRow = destSheet.createRow(i);
			}
			// warn 不能设置格式，否则客户模板的格式会被覆盖
			// destRow.setHeight(srcRow.getHeight());
			copySheetRow(srcRow, destRow,srcSheet,destSheet,srcwb, destwb,isCopyStyle);
			srcRow = null;
			destRow = null;
		}
		srcRow = null;
		destRow = null;
		return maxCellNum;
	}

	private static void copySheetRow(Row srcRow, Row destRow, Sheet srcSheet, Sheet destSheet,Workbook srcwb,
			Workbook destwb,boolean isCopyStyle) {
		FormulaEvaluator evaluator = srcwb.getCreationHelper().createFormulaEvaluator();
		int cellCount = srcRow.getLastCellNum();// 每行的总列数
		Cell srcCell = null, destCell = null;
		for (int j = 0; j < cellCount; j++) {// 遍历行单元格
			srcCell = srcRow.getCell(j);
			destCell = destRow.getCell(j);
			if (destCell == null) {
				destCell = destRow.createCell(j);
			}
//			if (destCell.getCellTypeEnum().equals(CellType.FORMULA)) {
//				destCell.setCellType(CellType.STRING);
//			}
			if (srcCell != null) {
				/*
				 * if(j == 0){ destCellStyle = null; destCellStyle =
				 * destCell.getCellStyle(); } // 复制样式
				 * destCellStyle.cloneStyleFrom(destCellStyle);
				 */
				if (isCopyStyle) {
					CellStyle style = srcCell.getCellStyle();

					if (null != style) {
						CellStyle newstyle = destwb.createCellStyle();
						newstyle.cloneStyleFrom(style);
						destCell.setCellStyle(newstyle);
						// cell.getCellStyle().cloneStyleFrom(style);
					}
					destRow.setHeight(srcRow.getHeight());
					destSheet.setColumnWidth(j,srcSheet.getColumnWidth( j ));
				}
				// 处理单元格内容
				setExcelCellResultValue(destCell, srcCell, evaluator);
				
//				switch (srcCell.getCellType()) {
//				case Cell.CELL_TYPE_STRING:  
//					destCell.setCellValue(srcCell.getRichStringCellValue());     
//					break;  
//					// 这里判断是否是日期  
//				case Cell.CELL_TYPE_NUMERIC:  
//					// 判断是否是日期格式  
//					// 测试发现如果这里不新建样式,日期显示的是数字  
//					if (DateUtil.isCellDateFormatted(srcCell)) {  
//						destCell.setCellValue(srcCell.getDateCellValue());  
//					} else {  
//						destCell.setCellValue(srcCell.getNumericCellValue());  
//					}  
//					break;  
//				case Cell.CELL_TYPE_FORMULA:  
//					destCell.setCellFormula(srcCell.getCellFormula());
//					break;  
//				case Cell.CELL_TYPE_BOOLEAN:  
//					destCell.setCellValue(srcCell.getBooleanCellValue());  
//					break;  
//				case Cell.CELL_TYPE_BLANK:  
//					destCell.setCellType(XSSFCell.CELL_TYPE_BLANK);  
//					break;  
//				case Cell.CELL_TYPE_ERROR:  
//					break;  
//				default:  
//					break;  
//				}  
			}
		}
		srcCell = null;
		destCell = null;
	}

	static void setExcelCellResultValue(Cell newCell, Cell cell, FormulaEvaluator evaluator) {

		if (cell == null) {
			return;
		}
		CellType type = cell.getCellTypeEnum();
		switch (type) {
			case BLANK:
				break;
			case BOOLEAN:
				newCell.setCellValue(cell.getBooleanCellValue());
				break;
			case ERROR:
				if(cell instanceof XSSFCell && newCell instanceof XSSFCell){
					newCell.setCellValue(((XSSFCell)cell).getErrorCellString());
				}else if(cell instanceof HSSFCell && newCell instanceof HSSFCell){
					newCell.setCellValue(cell.getErrorCellValue()+"");
				}
				break;
			case FORMULA: {
				CellValue cv = evaluator.evaluate(cell);
				switch (cv.getCellTypeEnum()) {
					case BLANK:
						break;
					case BOOLEAN:
						newCell.setCellValue(cv.getBooleanValue());
						break;
					case ERROR:
						newCell.setCellValue(cv.formatAsString());
						break;
					case NUMERIC:
						newCell.setCellValue(cv.getNumberValue());
						break;
					case STRING:
						newCell.setCellValue(cv.getStringValue());
						break;
					default:
						newCell.setCellValue(cv.formatAsString());
						break;
				}
				break;
			}
			case NUMERIC:
				// 判断是否是日期格式  
				// 测试发现如果这里不新建样式,日期显示的是数字  
				if (DateUtil.isCellDateFormatted(cell)) {  
					newCell.setCellValue(cell.getDateCellValue());  
				} else {  
					newCell.setCellValue(cell.getNumericCellValue());  
				}  
				//newCell.setCellValue(cell.getNumericCellValue());
				break;
			case STRING:
				newCell.setCellValue(cell.getStringCellValue());
				break;
			default:
				break;
		}
	}
	
	public static void mergeSheetAllRegion(Sheet srcSheet, Sheet destSheet) {
		int num = srcSheet.getNumMergedRegions();
		CellRangeAddress cellR = null;
		for (int i = 0; i < num; i++) {
			cellR = srcSheet.getMergedRegion(i);
			destSheet.addMergedRegion(cellR);
		}
	}

	public static void setSheetWidth(Sheet srcSheet, Sheet destSheet,
			int maxCellNum) {
		for (int i = 0; i <= maxCellNum; i++) {
			destSheet.setColumnWidth(i, srcSheet.getColumnWidth(i));
		}
	}
	
	public static void removeRow(Sheet sheet, int rowIndex) {
		int lastRowNum = sheet.getLastRowNum();
		if (rowIndex >= 0 && rowIndex < lastRowNum)
			sheet.shiftRows(rowIndex + 1, lastRowNum, -1);// 将行号为rowIndex+1一直到行号为lastRowNum的单元格全部上移一行，以便删除rowIndex行
		if (rowIndex == lastRowNum) {
			Row removingRow = sheet.getRow(rowIndex);
			if (removingRow != null)
				sheet.removeRow(removingRow);
		}
	}
}